import { defineEventHandler, getQuery, readBody } from 'h3'
import { query, execute } from '../utils/db'
import { customAlphabet } from 'nanoid'
import mysql from 'mysql2'
import jwt from 'jsonwebtoken'
const { verify } = jwt
import type { Database } from '~/types/page-admin'
import { ResponseCode, sendSuccessResponse, sendErrorResponse } from '~/server/utils/response'
import upsertRows from '@/utils/db-upsert'

const alphabet = process.env.alphabet || '123456789abcdefghijklmnopqrstuvwxyz'
const nanoid = customAlphabet(alphabet, 10)

export default defineEventHandler(async (event) => {
  const method = event.node.req.method

  // 获取首页数据 (GET /api/home)
  if (method === 'GET') {
    try {
      // 1. 批量查询所有主表和子表数据
      const [
        sliderRes,
        companyIntroRes,
        aboutUsRes,
        aboutStatsRes,
        productsRes,
        productItemsRes,
        solutionsRes,
        solutionItemsRes,
        solutionPointsRes,
        newsRes,
        newsItemsRes,
      ] = await Promise.all([
        // 轮播
        query<mysql.RowDataPacket[]>(`SELECT id, title, description, image_url, bg_color, sort_order FROM web2_slider ORDER BY sort_order ASC, id ASC`),
        // 企业介绍
        query<mysql.RowDataPacket[]>(`SELECT id, title, description, bg_color FROM web2_company_intro ORDER BY id DESC`),
        // 关于我们
        query<mysql.RowDataPacket[]>(`SELECT id, title, image_url, desc_title, desc1, desc2, bg_color FROM web2_about_us ORDER BY id DESC`),
        // 关于我们统计项
        query<mysql.RowDataPacket[]>(`SELECT id, about_id, title, value, sort_order FROM web2_about_stats ORDER BY about_id, sort_order, id`),
        // 产品服务
        query<mysql.RowDataPacket[]>(`SELECT id, title, description FROM web2_products ORDER BY id DESC`),
        // 产品项
        query<mysql.RowDataPacket[]>(`SELECT id, product_id, title, description,features, image_url, sort_order FROM web2_product_items ORDER BY product_id, sort_order, id`),
        // 解决方案
        query<mysql.RowDataPacket[]>(`SELECT id, title, description FROM web2_solutions ORDER BY id DESC`),
        // 解决方案子项
        query<mysql.RowDataPacket[]>(`SELECT id, solution_id, title, description, image_url, sort_order FROM web2_solution_items ORDER BY solution_id, sort_order, id`),
        // 解决方案描述点
        query<mysql.RowDataPacket[]>(`SELECT id, solution_item_id, point, sort_order FROM web2_solution_points ORDER BY solution_item_id, sort_order, id`),
        // 新闻与活动
        query<mysql.RowDataPacket[]>(`SELECT id, title, description FROM web2_news ORDER BY id DESC`),
        // 关于新闻项
        query<mysql.RowDataPacket[]>(`SELECT id, news_id, title, description, image_url, publish_date, tag, sort_order FROM web2_news_items ORDER BY news_id, sort_order, id`)
      ])

      // 2. 组装关于我们及统计项
      const aboutStatsMap = new Map<number, any[]>()
      for (const stat of aboutStatsRes.results) {
        if (!aboutStatsMap.has(stat.about_id)) aboutStatsMap.set(stat.about_id, [])
        aboutStatsMap.get(stat.about_id)!.push(stat)
      }
      const aboutUsList = aboutUsRes.results.map(about => ({
        ...about,
        stats: aboutStatsMap.get(about.id) || []
      }))

      // 3. 组装产品服务及产品项
      const productItemsMap = new Map<number, any[]>()
      for (const item of productItemsRes.results) {
        if (!productItemsMap.has(item.product_id)) productItemsMap.set(item.product_id, [])
        productItemsMap.get(item.product_id)!.push(item)
      }
      const productsList = productsRes.results.map(product => ({
        ...product,
        items: productItemsMap.get(product.id) || []
      }))

      // 4. 组装解决方案及子项和描述点
      const solutionPointsMap = new Map<number, any[]>()
      for (const point of solutionPointsRes.results) {
        if (!solutionPointsMap.has(point.solution_item_id)) solutionPointsMap.set(point.solution_item_id, [])
        solutionPointsMap.get(point.solution_item_id)!.push(point)
      }
      const solutionItemsMap = new Map<number, any[]>()
      for (const item of solutionItemsRes.results) {
        item.points = solutionPointsMap.get(item.id) || []
        if (!solutionItemsMap.has(item.solution_id)) solutionItemsMap.set(item.solution_id, [])
        solutionItemsMap.get(item.solution_id)!.push(item)
      }
      const solutionsList = solutionsRes.results.map(sol => ({
        ...sol,
        items: solutionItemsMap.get(sol.id) || []
      }))

      // 组装新闻项
      const newsItemsMap = new Map<number, any[]>()
      for (const item of newsItemsRes.results) {
        if (!newsItemsMap.has(item.news_id)) newsItemsMap.set(item.news_id, [])
        newsItemsMap.get(item.news_id)!.push(item)
      }
      const newsList = newsRes.results.map(news => ({
        ...news,
        items: newsItemsMap.get(news.id) || []
      }))

      // 5. 返回数据
      return sendSuccessResponse(event, {
        web2_slider: sliderRes.results,
        web2_company_intro: companyIntroRes.results[0] || {},
        web2_about_us: aboutUsList[0] || {},
        web2_products: productsList[0] || {},
        web2_solutions: solutionsList[0] || {},
        web2_news: newsList[0] || {},
      }, '首页列表获取成功')
    } catch (error: any) {
      return sendErrorResponse(event, ResponseCode.BAD_REQUEST, error || '更新首页数据失败')
    }
  }

  // 更新首页数据 (PUT /api/home)
  if (method === 'PUT') {
    const { web2_about_us, web2_news, web2_company_intro, web2_products, web2_slider, web2_solutions } = await readBody<Database>(event)

    try {
      // 关于我们
      if (web2_about_us) {
        await upsertRows('web2_about_us', [web2_about_us], ['id', 'stats'])
        if (Array.isArray(web2_about_us.stats)) {
          const aboutId = web2_about_us.id
          const statsWithFk = web2_about_us.stats.map(stat => ({
            ...stat,
            about_id: aboutId
          }))
          await upsertRows('web2_about_stats', statsWithFk, ['id'], 'about_id', aboutId)
        }
      }

      // 新闻与活动
      if (web2_news) {
        await upsertRows('web2_news', [web2_news], ['id', 'items'])
        if (Array.isArray(web2_news.items)) {
          const newsId = web2_news.id
          const itemsWithFk = web2_news.items.map(item => ({
            ...item,
            news_id: newsId,
            publish_date: item.publish_date ? String(item.publish_date).slice(0, 10) : null
          }))
          await upsertRows('web2_news_items', itemsWithFk, ['id'])
        }
      }

      // 产品服务
      if (web2_products) {
        await upsertRows('web2_products', [web2_products], ['id', 'items'])
        if (Array.isArray(web2_products.items)) {
          const productId = web2_products.id
          const itemsWithFk = web2_products.items.map(item => ({
            ...item,
            product_id: productId
          }))
          await upsertRows('web2_product_items', itemsWithFk, ['id'], 'product_id', productId)
        }
      }

      // 轮播图
      if (Array.isArray(web2_slider)) {
        await upsertRows('web2_slider', web2_slider)
      }

      // 解决方案
      if (web2_solutions) {
        await upsertRows('web2_solutions', [web2_solutions], ['id', 'items'])
        if (Array.isArray(web2_solutions.items)) {
          // 1. 先批量 upsert 所有子项
          const itemsWithFk = web2_solutions.items.map(item => ({
            ...item,
            solution_id: web2_solutions.id
          }))
          await upsertRows('web2_solution_items', itemsWithFk, ['id', 'points'], 'solution_id', web2_solutions.id)

          // 2. 再分别 upsert 每个子项的 points
          for (const item of web2_solutions.items) {
            if (Array.isArray(item.points)) {
              const solutionItemId = item.id
              const pointsWithFk = item.points.map(point => ({
                ...point,
                solution_item_id: solutionItemId
              }))
              await upsertRows('web2_solution_points', pointsWithFk, ['id'], 'solution_item_id', solutionItemId)
            }
          }
        }
      }


      // 企业介绍
      if (web2_company_intro) {
        await upsertRows('web2_company_intro', [web2_company_intro], ['id'])
      }

      //返回成功响应（添加更详细的信息）
      return sendSuccessResponse(event, {
        updatedAt: new Date().toISOString(),
        web2_about_us, web2_news, web2_company_intro, web2_products, web2_slider, web2_solutions
      }, '首页数据更新成功')

    } catch (error: any) {
      console.error('更新失败:', error)
      return sendErrorResponse(event, ResponseCode.BAD_REQUEST, error || '更新首页数据失败')
      // throw createError({
      //   statusCode: 500,
      //   message: '更新首页数据失败',
      //   data: {
      //     error: error.message,
      //     // 开发环境显示更多信息
      //     ...(process.env.NODE_ENV === 'development' && {
      //       stack: error.stack
      //     })
      //   }
      // })
    }
  }

  // 未匹配的路由
  // throw createError({
  //   statusCode: 404,
  //   message: 'Endpoint not found'
  // })
  return sendErrorResponse(event, ResponseCode.NOT_FOUND, 'Endpoint not found')

})
